--drop table if exists navinfo_stage_0f38;

--创建外部表
create external table if not exists navinfo_stage_0f38 (
time string,
latitude string,
longitude string,
height string,
direction string,
speed string,
routeStartTime string,
routeEndTime string,
driveCycleMileage string,
idlingMileage string,
paringRangeMileage string,
parkingIdleTime string,
brakeNumber string,
cumulativeMileage string,
brakingTime string,
vehicleEstimatedLoad string,
initialTotalFuelConsumption string,
terminationTotalFuelConsumption string,
cruiseRange string,
averageSpeedInterval string,
overSpeedTimes string,
idlingFuelConsumption string,
parkingIdleFuelConsumption string,
version string,
retransmissionPackets string,
protocolVersion string,
routeStopFlag string,
terminalId string,
routeStartMileage string,
routeStartFuelConsumption string,
routeStartLatitude string,
routeStartLongitude string,
routeStartHeight string,
routeStartDirection string,
routeEndMileage string,
routeEndFuelConsumption string,
routeEndLatitude string,
routeEndLongitude string,
routeEndHeight string,
routeEndDirection string,
routeIntegralMileage string,
routeGpsIntegralMileage string,
integralMileage string,
gpsIntegralMileage string,
vehicleTotalMileage string,
sharpCurveFrequency string,
sharpCurveMileage string,
routeIntegralFuelConsumption string,
routeTotalFuelConsumption string,
averageFuelConsumption string,
overSpeedDistance string,
overSpeedFuelConsumption string,
averageSpeedExcludeIdleTime string,
averageSpeedIncludeIdleTime string,
maxSpeed string,
parkingPowerOnTime string,
cityDrivingMileage string,
cityDrivingTime string,
cityDrivingFuelConsumption string,
ruralDrivingMileage string,
ruralDrivingTime string,
ruralDrivingFuelConsumption string,
ecoDrivingMileage string,
ecoDrivingTime string,
ecoDrivingFuelConsumption string,
upSlopeDrivingMileage string,
upSlopeDrivingTime string,
upSlopeDrivingFuelConsumption string,
downSlopeDrivingMileage string,
downSlopeDrivingTime string,
downSlopeDrivingFuelConsumption string,
levelRoadDrivingMileage string,
levelRoadDrivingTime string,
levelRoadDrivingFuelConsumption string,
coldEngineSharpDrivingMileage string,
coldEngineSharpDrivingTime string,
coldEngineSharpDrivingFuelConsumption string,
cruiseControlDrivingTime string,
cruiseControlDrivingFuelConsumption string,
engineOverSpeedDrivingMileage string,
engineOverSpeedDrivingTime string,
engineOverSpeedDrivingFuelConsumption string,
shiftGearsFrequency string,
sharpDownSpeedTime string,
sharpDownSpeedMileage string,
sharpDownSpeedFrequency string,
sharpUpSpeedTime string,
sharpUpSpeedMileage string,
sharpUpSpeedFrequency string,
sharpUpSpeedFuelConsumption string,
sharpStepOnAcceleratorMileage string,
sharpStepOnAcceleratorTime string,
sharpStepOnAcceleratorFuelConsumption string,
vehicleParkFrequency string,
vehicleStartFrequency string,
vehicleColdStartFrequency string,
fuelChargePercent string,
fuelChargeNumber string,
fuelLeakPercent string,
fuelLeakNumber string,
vehicleStartTime string,
vehicleStartFuelConsumption string,
acceleratorStabilityFrequency string,
acceleratorStabilityTime string,
instrumentFuelConsumption string,
clutchSwitchTime string,
maxEngineRotation string,
averageEngineRotation string,
averageAccelerator string,
speedRange01Mileage string,
speedRange01Time string,
speedRange01FuelConsumption string,
speedRange02Mileage string,
speedRange02Time string,
speedRange02FuelConsumption string,
speedRange03Mileage string,
speedRange03Time string,
speedRange03FuelConsumption string,
speedRange04Mileage string,
speedRange04Time string,
speedRange04FuelConsumption string,
speedRange05Mileage string,
speedRange05Time string,
speedRange05FuelConsumption string,
speedRange06Mileage string,
speedRange06Time string,
speedRange06FuelConsumption string,
speedRange07Mileage string,
speedRange07Time string,
speedRange07FuelConsumption string,
speedRange08Mileage string,
speedRange08Time string,
speedRange08FuelConsumption string,
speedRange09Mileage string,
speedRange09Time string,
speedRange09FuelConsumption string,
speedRange10Mileage string,
speedRange10Time string,
speedRange10FuelConsumption string,
speedRange11Mileage string,
speedRange11Time string,
speedRange11FuelConsumption string,
speedRange12Mileage string,
speedRange12Time string,
speedRange12FuelConsumption string,
exceedSpeedRangeMileage string,
exceedSpeedRangeTime string,
exceedSpeedRangeFuelConsumption string,
assistBrakeMileage string,
assistBrakeTime string,
fatigueDrivingFrequency string,
parkingStepOnAcceleratorTime string,
parkingStepOnAcceleratorFuelConsumption string,
engineWorkTime string,
drivingHandbrakeUseFrequency string,
drivingHandbrakeUseTime string,
drivingHandbrakeUseMileage string,
parkingIdleHandbrakeUseFrequency string,
parkingIdleHandbrakeUseTime string,
gear01UseRatio string,
gear01Mileage string,
gear01FuelConsumption string,
gear01Time string,
gear01Frequency string,
gear02UseRatio string,
gear02Mileage string,
gear02FuelConsumption string,
gear02Time string,
gear02Frequency string,
gear03UseRatio string,
gear03Mileage string,
gear03FuelConsumption string,
gear03Time string,
gear03Frequency string,
gear04UseRatio string,
gear04Mileage string,
gear04FuelConsumption string,
gear04Time string,
gear04Frequency string,
gear05UseRatio string,
gear05Mileage string,
gear05FuelConsumption string,
gear05Time string,
gear05Frequency string,
gear06UseRatio string,
gear06Mileage string,
gear06FuelConsumption string,
gear06Time string,
gear06Frequency string,
gear07UseRatio string,
gear07Mileage string,
gear07FuelConsumption string,
gear07Time string,
gear07Frequency string,
gear08UseRatio string,
gear08Mileage string,
gear08FuelConsumption string,
gear08Time string,
gear08Frequency string,
gear09UseRatio string,
gear09Mileage string,
gear09FuelConsumption string,
gear09Time string,
gear09Frequency string,
gear10UseRatio string,
gear10Mileage string,
gear10FuelConsumption string,
gear10Time string,
gear10Frequency string,
gear11UseRatio string,
gear11Mileage string,
gear11FuelConsumption string,
gear11Time string,
gear11Frequency string,
gear12UseRatio string,
gear12Mileage string,
gear12FuelConsumption string,
gear12Time string,
gear12Frequency string,
gear13UseRatio string,
gear13Mileage string,
gear13FuelConsumption string,
gear13Time string,
gear13Frequency string,
gear14UseRatio string,
gear14Mileage string,
gear14FuelConsumption string,
gear14Time string,
gear14Frequency string,
gear15UseRatio string,
gear15Mileage string,
gear15FuelConsumption string,
gear15Time string,
gear15Frequency string,
gear16UseRatio string,
gear16Mileage string,
gear16FuelConsumption string,
gear16Time string,
gear16Frequency string,
gearOtherUseRatio string,
gearOtherMileage string,
gearOtherFuelConsumption string,
gearOtherTime string,
gearOtherFrequency string,
engineSpeedRange01Mileage string,
engineSpeedRange01Time string,
engineSpeedRange01FuelConsumption string,
engineSpeedRange01UseRatio string,
engineSpeedRange01MileageRatio string,
engineSpeedRange01FuelConsumptionRatio string,
engineSpeedRange02Mileage string,
engineSpeedRange02Time string,
engineSpeedRange02FuelConsumption string,
engineSpeedRange02UseRatio string,
engineSpeedRange02MileageRatio string,
engineSpeedRange02FuelConsumptionRatio string,
engineSpeedRange03Mileage string,
engineSpeedRange03Time string,
engineSpeedRange03FuelConsumption string,
engineSpeedRange03UseRatio string,
engineSpeedRange03MileageRatio string,
engineSpeedRange03FuelConsumptionRatio string,
engineSpeedRange04Mileage string,
engineSpeedRange04Time string,
engineSpeedRange04FuelConsumption string,
engineSpeedRange04UseRatio string,
engineSpeedRange04MileageRatio string,
engineSpeedRange04FuelConsumptionRatio string,
engineSpeedRange05Mileage string,
engineSpeedRange05Time string,
engineSpeedRange05FuelConsumption string,
engineSpeedRange05UseRatio string,
engineSpeedRange05MileageRatio string,
engineSpeedRange05FuelConsumptionRatio string,
engineSpeedRange06Mileage string,
engineSpeedRange06Time string,
engineSpeedRange06FuelConsumption string,
engineSpeedRange06UseRatio string,
engineSpeedRange06MileageRatio string,
engineSpeedRange06FuelConsumptionRatio string,
exceedEngineSpeedRangeMileage string,
exceedEngineSpeedRangeTime string,
exceedEngineSpeedRangeFuelConsumption string,
exceedEngineSpeedRangeUseRatio string,
exceedEngineSpeedRangeMileageRatio string,
exceedEngineSpeedRangeFuelConsumptionRatio string,
carbonDioxideEmissions string,
routingCase string,
extendField01 string,
extendField02 string,
extendField03 string,
extendField04 string,
extendField05 string,
extendField06 string,
extendField07 string,
extendField08 string,
extendField09 string,
extendField10 string,
ecoDrivingTimeRatio string,
sharpStepOnAcceleratorFrequency string,
idlingFrequency string,
paringRangeFrequency string,
part_time int
) row format delimited fields terminated by '\001' STORED AS TEXTFILE location '/user/data/stage_import_0f38';

--创建目标表

--drop table navinfo_parquet_0f38

create table if not exists navinfo_parquet_0f38(
time string,
latitude string,
longitude string,
height string,
direction string,
speed string,
routeStartTime string,
routeEndTime string,
driveCycleMileage string,
idlingMileage string,
paringRangeMileage string,
parkingIdleTime string,
brakeNumber string,
cumulativeMileage string,
brakingTime string,
vehicleEstimatedLoad string,
initialTotalFuelConsumption string,
terminationTotalFuelConsumption string,
cruiseRange string,
averageSpeedInterval string,
overSpeedTimes string,
idlingFuelConsumption string,
parkingIdleFuelConsumption string,
version string,
retransmissionPackets string,
protocolVersion string,
routeStopFlag string,
terminalId string,
routeStartMileage string,
routeStartFuelConsumption string,
routeStartLatitude string,
routeStartLongitude string,
routeStartHeight string,
routeStartDirection string,
routeEndMileage string,
routeEndFuelConsumption string,
routeEndLatitude string,
routeEndLongitude string,
routeEndHeight string,
routeEndDirection string,
routeIntegralMileage string,
routeGpsIntegralMileage string,
integralMileage string,
gpsIntegralMileage string,
vehicleTotalMileage string,
sharpCurveFrequency string,
sharpCurveMileage string,
routeIntegralFuelConsumption string,
routeTotalFuelConsumption string,
averageFuelConsumption string,
overSpeedDistance string,
overSpeedFuelConsumption string,
averageSpeedExcludeIdleTime string,
averageSpeedIncludeIdleTime string,
maxSpeed string,
parkingPowerOnTime string,
cityDrivingMileage string,
cityDrivingTime string,
cityDrivingFuelConsumption string,
ruralDrivingMileage string,
ruralDrivingTime string,
ruralDrivingFuelConsumption string,
ecoDrivingMileage string,
ecoDrivingTime string,
ecoDrivingFuelConsumption string,
upSlopeDrivingMileage string,
upSlopeDrivingTime string,
upSlopeDrivingFuelConsumption string,
downSlopeDrivingMileage string,
downSlopeDrivingTime string,
downSlopeDrivingFuelConsumption string,
levelRoadDrivingMileage string,
levelRoadDrivingTime string,
levelRoadDrivingFuelConsumption string,
coldEngineSharpDrivingMileage string,
coldEngineSharpDrivingTime string,
coldEngineSharpDrivingFuelConsumption string,
cruiseControlDrivingTime string,
cruiseControlDrivingFuelConsumption string,
engineOverSpeedDrivingMileage string,
engineOverSpeedDrivingTime string,
engineOverSpeedDrivingFuelConsumption string,
shiftGearsFrequency string,
sharpDownSpeedTime string,
sharpDownSpeedMileage string,
sharpDownSpeedFrequency string,
sharpUpSpeedTime string,
sharpUpSpeedMileage string,
sharpUpSpeedFrequency string,
sharpUpSpeedFuelConsumption string,
sharpStepOnAcceleratorMileage string,
sharpStepOnAcceleratorTime string,
sharpStepOnAcceleratorFuelConsumption string,
vehicleParkFrequency string,
vehicleStartFrequency string,
vehicleColdStartFrequency string,
fuelChargePercent string,
fuelChargeNumber string,
fuelLeakPercent string,
fuelLeakNumber string,
vehicleStartTime string,
vehicleStartFuelConsumption string,
acceleratorStabilityFrequency string,
acceleratorStabilityTime string,
instrumentFuelConsumption string,
clutchSwitchTime string,
maxEngineRotation string,
averageEngineRotation string,
averageAccelerator string,
speedRange01Mileage string,
speedRange01Time string,
speedRange01FuelConsumption string,
speedRange02Mileage string,
speedRange02Time string,
speedRange02FuelConsumption string,
speedRange03Mileage string,
speedRange03Time string,
speedRange03FuelConsumption string,
speedRange04Mileage string,
speedRange04Time string,
speedRange04FuelConsumption string,
speedRange05Mileage string,
speedRange05Time string,
speedRange05FuelConsumption string,
speedRange06Mileage string,
speedRange06Time string,
speedRange06FuelConsumption string,
speedRange07Mileage string,
speedRange07Time string,
speedRange07FuelConsumption string,
speedRange08Mileage string,
speedRange08Time string,
speedRange08FuelConsumption string,
speedRange09Mileage string,
speedRange09Time string,
speedRange09FuelConsumption string,
speedRange10Mileage string,
speedRange10Time string,
speedRange10FuelConsumption string,
speedRange11Mileage string,
speedRange11Time string,
speedRange11FuelConsumption string,
speedRange12Mileage string,
speedRange12Time string,
speedRange12FuelConsumption string,
exceedSpeedRangeMileage string,
exceedSpeedRangeTime string,
exceedSpeedRangeFuelConsumption string,
assistBrakeMileage string,
assistBrakeTime string,
fatigueDrivingFrequency string,
parkingStepOnAcceleratorTime string,
parkingStepOnAcceleratorFuelConsumption string,
engineWorkTime string,
drivingHandbrakeUseFrequency string,
drivingHandbrakeUseTime string,
drivingHandbrakeUseMileage string,
parkingIdleHandbrakeUseFrequency string,
parkingIdleHandbrakeUseTime string,
gear01UseRatio string,
gear01Mileage string,
gear01FuelConsumption string,
gear01Time string,
gear01Frequency string,
gear02UseRatio string,
gear02Mileage string,
gear02FuelConsumption string,
gear02Time string,
gear02Frequency string,
gear03UseRatio string,
gear03Mileage string,
gear03FuelConsumption string,
gear03Time string,
gear03Frequency string,
gear04UseRatio string,
gear04Mileage string,
gear04FuelConsumption string,
gear04Time string,
gear04Frequency string,
gear05UseRatio string,
gear05Mileage string,
gear05FuelConsumption string,
gear05Time string,
gear05Frequency string,
gear06UseRatio string,
gear06Mileage string,
gear06FuelConsumption string,
gear06Time string,
gear06Frequency string,
gear07UseRatio string,
gear07Mileage string,
gear07FuelConsumption string,
gear07Time string,
gear07Frequency string,
gear08UseRatio string,
gear08Mileage string,
gear08FuelConsumption string,
gear08Time string,
gear08Frequency string,
gear09UseRatio string,
gear09Mileage string,
gear09FuelConsumption string,
gear09Time string,
gear09Frequency string,
gear10UseRatio string,
gear10Mileage string,
gear10FuelConsumption string,
gear10Time string,
gear10Frequency string,
gear11UseRatio string,
gear11Mileage string,
gear11FuelConsumption string,
gear11Time string,
gear11Frequency string,
gear12UseRatio string,
gear12Mileage string,
gear12FuelConsumption string,
gear12Time string,
gear12Frequency string,
gear13UseRatio string,
gear13Mileage string,
gear13FuelConsumption string,
gear13Time string,
gear13Frequency string,
gear14UseRatio string,
gear14Mileage string,
gear14FuelConsumption string,
gear14Time string,
gear14Frequency string,
gear15UseRatio string,
gear15Mileage string,
gear15FuelConsumption string,
gear15Time string,
gear15Frequency string,
gear16UseRatio string,
gear16Mileage string,
gear16FuelConsumption string,
gear16Time string,
gear16Frequency string,
gearOtherUseRatio string,
gearOtherMileage string,
gearOtherFuelConsumption string,
gearOtherTime string,
gearOtherFrequency string,
engineSpeedRange01Mileage string,
engineSpeedRange01Time string,
engineSpeedRange01FuelConsumption string,
engineSpeedRange01UseRatio string,
engineSpeedRange01MileageRatio string,
engineSpeedRange01FuelConsumptionRatio string,
engineSpeedRange02Mileage string,
engineSpeedRange02Time string,
engineSpeedRange02FuelConsumption string,
engineSpeedRange02UseRatio string,
engineSpeedRange02MileageRatio string,
engineSpeedRange02FuelConsumptionRatio string,
engineSpeedRange03Mileage string,
engineSpeedRange03Time string,
engineSpeedRange03FuelConsumption string,
engineSpeedRange03UseRatio string,
engineSpeedRange03MileageRatio string,
engineSpeedRange03FuelConsumptionRatio string,
engineSpeedRange04Mileage string,
engineSpeedRange04Time string,
engineSpeedRange04FuelConsumption string,
engineSpeedRange04UseRatio string,
engineSpeedRange04MileageRatio string,
engineSpeedRange04FuelConsumptionRatio string,
engineSpeedRange05Mileage string,
engineSpeedRange05Time string,
engineSpeedRange05FuelConsumption string,
engineSpeedRange05UseRatio string,
engineSpeedRange05MileageRatio string,
engineSpeedRange05FuelConsumptionRatio string,
engineSpeedRange06Mileage string,
engineSpeedRange06Time string,
engineSpeedRange06FuelConsumption string,
engineSpeedRange06UseRatio string,
engineSpeedRange06MileageRatio string,
engineSpeedRange06FuelConsumptionRatio string,
exceedEngineSpeedRangeMileage string,
exceedEngineSpeedRangeTime string,
exceedEngineSpeedRangeFuelConsumption string,
exceedEngineSpeedRangeUseRatio string,
exceedEngineSpeedRangeMileageRatio string,
exceedEngineSpeedRangeFuelConsumptionRatio string,
carbonDioxideEmissions string,
routingCase string,
extendField01 string,
extendField02 string,
extendField03 string,
extendField04 string,
extendField05 string,
extendField06 string,
extendField07 string,
extendField08 string,
extendField09 string,
extendField10 string,
ecoDrivingTimeRatio string,
sharpStepOnAcceleratorFrequency string,
idlingFrequency string,
paringRangeFrequency string
) partitioned by (part_time int) stored as Parquet TBLPROPERTIES('parquet.compression'='SNAPPY');
